package com.cqut.servlet;

import com.alibaba.fastjson.JSON;
import com.cqut.entity.achievement;
import com.cqut.utils.DBUtils;
import com.cqut.utils.ResultData;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class ServletAchievement extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html;charset=utf-8");
        req.setCharacterEncoding("utf-8");

        String action = req.getParameter("action") == null ? "" : req.getParameter("action");
        switch (action) {
            case"getdglist":
                getDglist(req,resp);
                break;
            case"add":
                Add(req,resp);
                break;
            case"exists":
                Exists(req,resp);
                break;
//            case"delete":
//                Delete(req,resp);
//                break;
            case"getone":
                getOne(req,resp);
                break;
            case"update":
                Update(req,resp);
                break;
            case"search":
                getSearch(req,resp);
                break;
            default:
                break;
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }

    //分页
    private void getDglist(HttpServletRequest req, HttpServletResponse resp) {

        try {
            //获取参数
            String pageIndex=req.getParameter("page")==null? "1":req.getParameter("page");
            String pageSize =req.getParameter("rows")==null? "10":req.getParameter("rows");

            // 将字符串转换为整数，并处理可能的异常
            int currentPage = Integer.parseInt(pageIndex);
            int pageSizeInt = Integer.parseInt(pageSize);

            // 确保页数和每页大小都是合理的值
            if (currentPage < 1) currentPage = 1;
            if (pageSizeInt < 1) pageSizeInt = 10;


            PrintWriter out = resp.getWriter();
            HashMap<String, Object> map = new HashMap<>();
            // 获取总记录数
            String sqlcount = "select count(*) from achievement";
            int total=Integer.parseInt(DBUtils.QueryScalar(sqlcount).toString());
            map.put("total", total);

            // 计算LIMIT子句的参数
            int offset = (currentPage - 1) * pageSizeInt;
            String sqllist = "SELECT * FROM ( " +
                    "  SELECT (@row_number:=@row_number + 1) AS rownumber, tmp_table.* " +
                    "  FROM ( SELECT * FROM achievement ORDER BY sno ASC ) AS tmp_table, (SELECT @row_number:=0) AS t " +
                    ") AS tmp_table " +
                    "WHERE rownumber > " + offset + " " +
                    "LIMIT " + pageSize;

            //问题在这一句
            List<achievement> list = DBUtils.QueryBeanList(sqllist, achievement.class);

            map.put("rows", list);
            out.write(JSON.toJSONString(map));
        }
        catch (Exception e){
            e.printStackTrace();
        }



//        try {
//            int pageIndex = Integer.parseInt(req.getParameter("page")==null ? "1" : req.getParameter("page"));
//            int pageSize = Integer.parseInt(req.getParameter("rows")==null ? "10" : req.getParameter("rows"));
//            PrintWriter out = resp.getWriter();
//            HashMap<String, Object> map = new HashMap<>();
//            String sqlcount = "select count(*) from buy";
//            int total = Integer.parseInt(DBUtils.QueryScalar(sqlcount).toString());
//            map.put("total",total);
//            String sqllist = "SELECT * FROM ( " +
//                    "  SELECT (@row_number:=@row_number + 1) AS rownumber, tmp_table.* " +
//                    "  FROM (SELECT * FROM buy ORDER BY buyperson ASC) AS tmp_table, (SELECT @row_number:=0) AS t " +
//                    ") AS tmp_table " +
//                    "WHERE rownumber > " + (pageIndex - 1) * pageSize + " " +
//                    "LIMIT " + pageSize;
//
//            List<buy> list = DBUtils.QueryBeanList(sqllist, buy.class);
//
//            map.put("rows", list);
//            out.write(JSON.toJSONString(map));
//        }catch (Exception e) {
//            e.printStackTrace();
//        }
    }

    //搜索功能
    private void getSearch(HttpServletRequest req, HttpServletResponse resp) {

        try {

            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("key1");

            HashMap<String, Object> map = new HashMap<>();

            int total=10;
            map.put("total", total);

            String sql = "select * from achievement where sno = ?";
            achievement achievement = DBUtils.QueryBean(sql, achievement.class, sno);

            if (achievement == null){
                out.write("404");
            }else {
                //rows属性的值似乎是一个对象，而不是一个对象数组。DataGrid通常期望rows是一个数组，每个数组元素代表一行数据。
                //需要搜索是数组，所以用list
                List<achievement> achievements = new ArrayList<>();
                if (achievement != null) {
                    achievements.add(achievement);
                }

                map.put("rows", achievements);
                String jsonString = JSON.toJSONString(map);

                out.write(jsonString);
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }

    }

//    //添加数据
    private void Add(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        try{
            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("sno");
            String sname = req.getParameter("sname");
            String cno= req.getParameter("cno");
            String cname= req.getParameter("cname");
            String teacher= req.getParameter("teacher");
            String semester= req.getParameter("semester");
            String grades= req.getParameter("grades");

            String sql = "insert into achievement (sno, sname, cno, cname,teacher,semester,grades) values(?,?,?,?,?,?,?)";
            if(DBUtils.Update(sql,sno,sname,cno,cname,teacher,semester,grades) > 0){
                out.write(JSON.toJSONString(new ResultData(String.valueOf(resp.getStatus()),"保存成功")));
            }
            else{
                out.write(JSON.toJSONString(new ResultData("501","保存失败")));
            }

        }catch (Exception e) {
            resp.getWriter().write(JSON.toJSONString(new ResultData("501","数据有误")));
        }
    }

    //判断编码是否存在

    private void Exists(HttpServletRequest req, HttpServletResponse resp) {
        try{
            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("sno");
            String sql = "select count(*) from achievement where sno = ?";
            int count = Integer.parseInt(DBUtils.QueryScalar(sql,sno).toString());
            if(count > 0){
//                out.write(JSON.toJSONString(new ResultData("200","数据已经存在")));
                out.write("false");
            }
            else
                out.write("true");
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

//    //删除行
//    private void Delete(HttpServletRequest req, HttpServletResponse resp) {
//        try{
//            PrintWriter out = resp.getWriter();
//            String sno = req.getParameter("sno");
//            String sql = "delete from achievement where sno = ?";
//            if(DBUtils.Update(sql,sno) >0){
//                out.write(JSON.toJSONString(new ResultData("200","删除成功")));
//            }
//            else{
//                out.write(JSON.toJSONString(new ResultData("500","删除失败")));
//            }
//        }catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    //获取选择行数据
    private void getOne(HttpServletRequest req, HttpServletResponse resp) {
        try{
            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("sno");
            String sql = "select * from achievement where sno = ?";
            achievement achievement = DBUtils.QueryBean(sql, achievement.class,sno);
            ResultData rd = new ResultData();
            if(achievement != null){
                rd.setCode(String.valueOf(resp.getStatus()));
                rd.setMsg("获取成功");
                rd.setData(achievement);
                out.write(JSON.toJSONString(rd));
            }
            else{
                rd.setCode("501");
                rd.setMsg("获取失败");
                out.write(JSON.toJSONString(rd));
            }

        }catch (Exception e) {
            e.printStackTrace();
        }
    }


    //修改行数据
    private void Update(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        try{
            PrintWriter out = resp.getWriter();
            String sname = req.getParameter("sname");
            String cno= req.getParameter("cno");
            String cname= req.getParameter("cname");
            String teacher= req.getParameter("teacher");
            String semester= req.getParameter("semester");
            String grades= req.getParameter("grades");
            String sno = req.getParameter("sno");
            String sql = "update achievement set  sname=?, cno=?, cname=?,teacher=?,semester=?,grades=? where sno=?";
            if(DBUtils.Update(sql,sname,cno,cname,teacher,semester,grades,sno) > 0){
                out.write(JSON.toJSONString(new ResultData(String.valueOf(resp.getStatus()),"保存成功")));
            }
            else{
                out.write(JSON.toJSONString(new ResultData("501","保存失败")));
            }

        }catch (Exception e) {
            resp.getWriter().write(JSON.toJSONString(new ResultData("501","数据有误")));
        }
    }

}
